﻿using System;
using System.Collections.Generic;
using System.Text;
using System.Data;
using System.Data.SqlClient;
using DBUtil;

namespace DAL
{
    public class PageQuery2005_DAL
    {

        #region --sql 2005 通用分页方法
        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>
        /// <param name="_OverPartitionField">分区字段</param>
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>
        /// <param name="_OrderField">辅助排序字段</param>
        /// <param name="_GroupField">分组字段</param>
        /// <param name="_HavingField">分组判断条件</param>
        /// <param name="_PageIndex">索引页码</param>
        /// <param name="_PageSize">页长</param>
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverPartitionField, string _OverOrderField, string _OrderField, string _GroupField, string _HavingField, int _PageIndex, int _PageSize, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),
                new SqlParameter("@OverPartitionField",SqlDbType.NVarChar,512),
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),
                new SqlParameter("@OrderField",SqlDbType.NVarChar,512),
                new SqlParameter("@GroupField",SqlDbType.NVarChar,512),
                new SqlParameter("@HavingField",SqlDbType.NVarChar,512),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Page",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverPartitionField;
                sqlparams[4].Value = _OverOrderField;
                sqlparams[5].Value = _OrderField;
                sqlparams[6].Value = _GroupField;
                sqlparams[7].Value = _HavingField;
                sqlparams[8].Value = _PageSize;
                sqlparams[9].Value = _PageIndex;
                sqlparams[10].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }


        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>     
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>
        /// <param name="_OrderField">辅助排序字段</param>
        /// <param name="_GroupField">分组字段</param>
        /// <param name="_HavingField">分组判断条件</param>
        /// <param name="_PageIndex">索引页码</param>
        /// <param name="_PageSize">页长</param>
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverOrderField, string _OrderField, string _GroupField, string _HavingField, int _PageIndex, int _PageSize, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),               
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),
                new SqlParameter("@OrderField",SqlDbType.NVarChar,512),
                new SqlParameter("@GroupField",SqlDbType.NVarChar,512),
                new SqlParameter("@HavingField",SqlDbType.NVarChar,512),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Page",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverOrderField;
                sqlparams[4].Value = _OrderField;
                sqlparams[5].Value = _GroupField;
                sqlparams[6].Value = _HavingField;
                sqlparams[7].Value = _PageSize;
                sqlparams[8].Value = _PageIndex;
                sqlparams[9].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }


        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>     
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>   
        /// <param name="_GroupField">分组字段</param>
        /// <param name="_HavingField">分组判断条件</param>
        /// <param name="_PageIndex">索引页码</param>
        /// <param name="_PageSize">页长</param>
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverOrderField, string _GroupField, string _HavingField, int _PageIndex, int _PageSize, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),               
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),              
                new SqlParameter("@GroupField",SqlDbType.NVarChar,512),
                new SqlParameter("@HavingField",SqlDbType.NVarChar,512),
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Page",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverOrderField;
                sqlparams[4].Value = _GroupField;
                sqlparams[5].Value = _HavingField;
                sqlparams[6].Value = _PageSize;
                sqlparams[7].Value = _PageIndex;
                sqlparams[8].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }


        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>     
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>   
        /// <param name="_GroupField">分组字段</param>    
        /// <param name="_PageIndex">索引页码</param>
        /// <param name="_PageSize">页长</param>
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverOrderField, string _GroupField, int _PageIndex, int _PageSize, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),               
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),              
                new SqlParameter("@GroupField",SqlDbType.NVarChar,512),             
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Page",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverOrderField;
                sqlparams[4].Value = _GroupField;
                sqlparams[5].Value = _PageSize;
                sqlparams[6].Value = _PageIndex;
                sqlparams[7].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }


        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>     
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>         
        /// <param name="_PageIndex">索引页码</param>
        /// <param name="_PageSize">页长</param>
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverOrderField, int _PageIndex, int _PageSize, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),               
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),     
                new SqlParameter("@PageSize",SqlDbType.Int),
                new SqlParameter("@Page",SqlDbType.Int),
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverOrderField;
                sqlparams[4].Value = _PageSize;
                sqlparams[5].Value = _PageIndex;
                sqlparams[6].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }


        /// <summary>
        /// sql 2005 通用分页，返回DataTable
        /// </summary>
        /// <param name="_pageCount">返回查询结果集总行数</param>
        /// <param name="_SelectField">查询字段，可以使用sum,count,join等</param>
        /// <param name="_FormTables">查询数据表名，包括架构名</param>
        /// <param name="_WhereField">查询Where条件语句、判断字段，调用的时候不要输入"where"</param>     
        /// <param name="_OverOrderField">主排序字段(必填) 如: _Field desc 、_Field asc</param>  
        /// <param name="_connection"></param>
        /// <param name="_strMess"></param>
        /// <returns></returns>
        public static DataTable SelectPage(out int _pageCount, string _SelectField, string _FormTables, string _WhereField, string _OverOrderField, string _connection, out string _strMess)
        {
            _pageCount = 0;
            DataTable _myTBl = new DataTable();
            try
            {
                SqlParameter[] sqlparams = {
                new SqlParameter("@SelectField",SqlDbType.NVarChar,1024),
                new SqlParameter("@FormTables",SqlDbType.NVarChar,1024),
                new SqlParameter("@WhereField",SqlDbType.NVarChar,1024),               
                new SqlParameter("@OverOrderField",SqlDbType.NVarChar,512),    
                new SqlParameter("@Counts",SqlDbType.Int)
                };
                sqlparams[0].Value = _SelectField;
                sqlparams[1].Value = _FormTables;
                sqlparams[2].Value = _WhereField;
                sqlparams[3].Value = _OverOrderField;
                sqlparams[4].Direction = ParameterDirection.Output;

                Sql_BaseDAL MySqlBase = new Sql_BaseDAL();
                MySqlBase.myParameter = sqlparams;
                MySqlBase.SqlConnectionString = _connection;
                _myTBl = MySqlBase.SqlProcedure_DataTable(out _strMess, "sys_TablePaginationSearch", true, out _pageCount);
            }
            catch (ArgumentException e)
            {
                _strMess = e.Message;
            }
            return _myTBl;
        }
        #endregion
    }
}
